package cn.stylefeng.mferp.modular.business.controller;

import cn.stylefeng.mferp.core.common.annotion.Permission;
import cn.stylefeng.mferp.core.common.exception.BizExceptionEnum;
import cn.stylefeng.mferp.core.excel.annotation.ExcelSheet;
import cn.stylefeng.mferp.core.log.LogObjectHolder;
import cn.stylefeng.mferp.core.util.ReflectUtil;
import cn.stylefeng.mferp.modular.business.model.*;
import cn.stylefeng.mferp.modular.business.service.IEasyExcelService;
import cn.stylefeng.mferp.modular.business.service.ITjlbService;
import cn.stylefeng.mferp.modular.business.warpper.TjlbWarpper;
import cn.stylefeng.roses.core.base.controller.BaseController;
import cn.stylefeng.roses.core.reqres.response.ResponseData;
import cn.stylefeng.roses.core.util.ToolUtil;
import cn.stylefeng.roses.kernel.model.exception.ServiceException;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.Exception;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * 生产记录控制器
 *
 * @author fengshuonan
 * @Date 2020-05-19 22:37:05
 */
@Controller
@RequestMapping("/tjlb")
public class TjlbController extends BaseController {

    private Logger logger = LoggerFactory.getLogger(TjlbController.class);
    private String PREFIX = "/business/tjlb/";

    @Value("${yzr.excel.export_path}")
    private String exportPath;
    @Value("${yzr.excel.file_name}")
    private String fileName;
    @Value("${yzr.excel.sheet_name}")
    private String sheetName;

    @Autowired
    private JdbcTemplate sqlServerJdbcTemplate;

    @Autowired
    private ITjlbService tjlbService;

    @Autowired
    private IEasyExcelService easyExcelService;

    /**
     * 跳转到生产记录首页
     */
    @RequestMapping("")
    public String index() {
        return PREFIX + "tjlb.html";
    }
    /**
     * 跳转到砂浆记录首页
     */
    @RequestMapping("/sj")
    public String indexSj() {
        return PREFIX + "tjlb_sj.html";
    }
    /**
     * 跳转到回弹生产记录首页
     */
    @RequestMapping("/ht")
    public String indexHt() {
        return PREFIX + "tjlb_ht.html";
    }
    /**
     * 跳转到方量统计首页
     */
    @RequestMapping("/fltj")
    public String indexFltj() {
        return PREFIX + "tjlb_fltj.html";
    }
    /**
     * 跳转到添加生产记录
     */
    @RequestMapping("/tjlb_add")
    public String tjlbAdd() {
        return PREFIX + "tjlb_add.html";
    }

    /**
     * 跳转到修改生产记录
     */
    @RequestMapping("/tjlb_update/{tjlbId}")
    public String tjlbUpdate(@PathVariable Integer tjlbId, Model model) {
        Tjlb tjlb = tjlbService.selectById(tjlbId);
        model.addAttribute("item",tjlb);
        LogObjectHolder.me().set(tjlb);
        return PREFIX + "tjlb_edit.html";
    }

    /**
     * 获取生产记录列表
     */
    @RequestMapping(value = "/list")
    @Permission
    @ResponseBody
    public Object list(@RequestParam(required = false) String beginTime,
                       @RequestParam(required = false) String endTime,
                       @RequestParam(required = false) String outBeginTime,
                       @RequestParam(required = false) String outEndTime,
                       @RequestParam(required = false) String inBeginTime,
                       @RequestParam(required = false) String inEndTime,
                       @RequestParam(required = false) String gcmc) {
        List<Map<String, Object>> tjlbs = tjlbService.selectTjlbs(beginTime, endTime, gcmc, outBeginTime, outEndTime, inBeginTime, inEndTime);
        return new TjlbWarpper(tjlbs).wrap();
    }

    /**
     * 获取砂浆生产记录列表
     */
    @RequestMapping(value = "/list_sj")
    @Permission
    @ResponseBody
    public Object listSj(@RequestParam(required = false) String beginTime,
                       @RequestParam(required = false) String endTime,
                       @RequestParam(required = false) String gcmc) {
        List<Map<String, Object>> tjlbs = tjlbService.selectTjlbSjs(beginTime, endTime, gcmc);
        return new TjlbWarpper(tjlbs).wrap();
    }

    /**
     * 获取回弹生产记录列表
     */
    @RequestMapping(value = "/list_ht")
    @Permission
    @ResponseBody
    public Object listHt(@RequestParam(required = false) String beginTime, @RequestParam(required = false) String endTime,
                         @RequestParam(required = false) String gcmc, @RequestParam(required = false) String jzbw,
                         @RequestParam(required = false) String rwdh, @RequestParam(required = false) String scbt,
                         @RequestParam(required = false) String tpz) {
        List<Map<String, Object>> tjlbs = tjlbService.selectTjlbHts(beginTime, endTime, gcmc, jzbw, rwdh, scbt, tpz);
        return new TjlbWarpper(tjlbs).wrap();
    }

    /**
     * 获取方量统计记录列表
     */
    @RequestMapping(value = "/list_fltj")
    @Permission
    @ResponseBody
    public Object listFltj(@RequestParam(required = false) String beginTime, @RequestParam(required = false) String endTime,
                         @RequestParam(required = false) String gcmc, @RequestParam(required = false) String jzbw,
                         @RequestParam(required = false) String rwdh, @RequestParam(required = false) String scbt,
                         @RequestParam(required = false) String tpz, @RequestParam(required = false) String fltjType) {
        if(StringUtils.isNotBlank(fltjType) && "2".equals(fltjType)) {
            //按日统计
            List<TjlbFltjModel> tjlbs = tjlbService.selectTjlbFltjDays(beginTime, endTime, gcmc, jzbw, rwdh, scbt, tpz);
            return tjlbs;
        } else if(StringUtils.isNotBlank(fltjType) && "3".equals(fltjType)) {
            //按年统计
            List<TjlbFltjModel> tjlbs = tjlbService.selectTjlbFltjYears(beginTime, endTime, gcmc, jzbw, rwdh, scbt, tpz);
            return tjlbs;
        } else {
            //按月统计
            List<TjlbFltjModel> tjlbs = tjlbService.selectTjlbFltjMonths(beginTime, endTime, gcmc, jzbw, rwdh, scbt, tpz);
            return tjlbs;
        }
    }

    /**
     * 修改生产记录
     */
    @RequestMapping(value = "/update")
    @ResponseBody
    public Object update(Tjlb tjlb) {
        tjlbService.updateById(tjlb);
        return SUCCESS_TIP;
    }

    /**
     * 查询生产记录
     */
    @RequestMapping(value = "/get_tjlb")
    @ResponseBody
    public Object getTjlb(@RequestParam String no) {
        return tjlbService.getTjlb(no);
    }

    /**
     * 生产记录详情
     */
    @RequestMapping(value = "/detail/{tjlbId}")
    public String detail(@PathVariable("tjlbId") Integer fno, Model model) {
        if (ToolUtil.isEmpty(fno)) {
            throw new ServiceException(BizExceptionEnum.REQUEST_NULL);
        }

        Tjlb item = tjlbService.selectByFno(fno);
        model.addAttribute("item", item);
        LogObjectHolder.me().set(item);
        return PREFIX + "tjlb_edit.html";
    }

    /**
     * 导出生产记录（停用）
     */
    @RequestMapping(value = "/export")
    @ResponseBody
    public Object export(@RequestParam(required = false) String beginTime,
                         @RequestParam(required = false) String endTime,
                         @RequestParam(required = false) String gcmc,
                         @RequestParam(required = false) String excelType) {
        List<Tjlb> tjlbs = tjlbService.selectTjlbList(beginTime, endTime, gcmc);
        List<TjlbModel> tjlbModels = new ArrayList<>();
        for (Tjlb tjlb: tjlbs) {
            TjlbModel tjlbModel = new TjlbModel();
            ReflectUtil.copyValue(tjlb, tjlbModel);
            if(StringUtils.isNotBlank(tjlbModel.getFgcmc())) {
                tjlbModels.add(tjlbModel);
            }
        }

        if(StringUtils.isNotBlank(excelType) && "07".equals(excelType)) {
            // 写法2
            String thisFileName = exportPath + fileName + System.currentTimeMillis() + ExcelTypeEnum.XLSX.getValue();
            //  这里 需要指定写用哪个class去写
            ExcelWriter excelWriter = EasyExcel.write(thisFileName, TjlbModel.class).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            excelWriter.write(tjlbModels, writeSheet);
            /// 千万别忘记finish 会帮忙关闭流
            excelWriter.finish();
            try{
                Runtime.getRuntime().exec("cmd /c start " + thisFileName);
            }catch(IOException e){}
        } else {
            // 写法1
            String thisFileName = exportPath + fileName + System.currentTimeMillis() + ExcelTypeEnum.XLS.getValue();

            // 如果这里想使用03 则 传入excelType参数即可
            EasyExcel.write(thisFileName, TjlbModel.class).excelType(ExcelTypeEnum.XLS).sheet(sheetName).doWrite(tjlbModels);
            try{
                Runtime.getRuntime().exec("cmd /c start " + thisFileName);
            }catch(IOException e){}
        }
        return SUCCESS_TIP;
    }

    /**
     * 导出对外生产记录（停用）
     */
    @RequestMapping(value = "/export_out")
    @ResponseBody
    public Object exportOut(@RequestParam(required = false) String beginTime,
                         @RequestParam(required = false) String endTime,
                         @RequestParam(required = false) String gcmc,
                         @RequestParam(required = false) String excelType) {
        List<Tjlb> tjlbs = tjlbService.selectTjlbListOut(beginTime, endTime, gcmc);
        List<TjlbModel> tjlbModels = new ArrayList<>();
        for (Tjlb tjlb: tjlbs) {
            if (Objects.equals(tjlb.getFzt(), "作废")) {
                continue;
            }
            TjlbModel tjlbModel = new TjlbModel();
            ReflectUtil.copyValue(tjlb, tjlbModel);
            if(StringUtils.isNotBlank(tjlbModel.getFgcmc())) {
                tjlbModels.add(tjlbModel);
            }
        }

        if(StringUtils.isNotBlank(excelType) && "07".equals(excelType)) {
            // 写法2
            String thisFileName = exportPath + fileName + System.currentTimeMillis() + ExcelTypeEnum.XLSX.getValue();
            //  这里 需要指定写用哪个class去写
            ExcelWriter excelWriter = EasyExcel.write(thisFileName, TjlbModel.class).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            excelWriter.write(tjlbModels, writeSheet);
            /// 千万别忘记finish 会帮忙关闭流
            excelWriter.finish();
            try{
                Runtime.getRuntime().exec("cmd /c start " + thisFileName);
            }catch(IOException e){}
        } else {
            // 写法1
            String thisFileName = exportPath + fileName + System.currentTimeMillis() + ExcelTypeEnum.XLS.getValue();

            // 如果这里想使用03 则 传入excelType参数即可
            EasyExcel.write(thisFileName, TjlbModel.class).excelType(ExcelTypeEnum.XLS).sheet(sheetName).doWrite(tjlbModels);
            try{
                Runtime.getRuntime().exec("cmd /c start " + thisFileName);
            }catch(IOException e){}
        }
        return SUCCESS_TIP;
    }

    /**
     * 导出选中的生产记录
     */
    @RequestMapping(value = "/export_checked")
    @ResponseBody
    public Object exportChecked(@RequestParam String selected, @RequestParam(required = false) String excelType) {
        logger.info("export_checked：", JSON.toJSONString(selected));
        JSONArray jsonArray = JSON.parseArray(selected);
        if(jsonArray.size() <= 0) {
            return ResponseData.error("选中项为空，导出失败");
        }
        List<String> list = jsonArray.toJavaList(String.class);
        List<Tjlb> tjlbs = tjlbService.selectTjlbListByItems(list);
        List<TjlbModel> tjlbModels = new ArrayList<>();
        for (Tjlb tjlb: tjlbs) {
            TjlbModel tjlbModel = new TjlbModel();
            ReflectUtil.copyValue(tjlb, tjlbModel);
            if(StringUtils.isNotBlank(tjlbModel.getFgcmc())) {
                tjlbModels.add(tjlbModel);
            }
        }
        if (StringUtils.isNotBlank(excelType) && "03".equals(excelType)) {
            // 写法1
            String thisFileName = exportPath + fileName + System.currentTimeMillis() + ExcelTypeEnum.XLS.getValue();

            // 如果这里想使用03 则 传入excelType参数即可
            EasyExcel.write(thisFileName, TjlbModel.class).excelType(ExcelTypeEnum.XLS).sheet(sheetName).doWrite(tjlbModels);
            try{
                Runtime.getRuntime().exec("cmd /c start " + thisFileName);
            }catch(IOException e){}
        } else {
            // 写法2
            String thisFileName = exportPath + fileName + System.currentTimeMillis() + ExcelTypeEnum.XLSX.getValue();
            //  这里 需要指定写用哪个class去写
            ExcelWriter excelWriter = EasyExcel.write(thisFileName, TjlbModel.class).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            excelWriter.write(tjlbModels, writeSheet);
            /// 千万别忘记finish 会帮忙关闭流
            excelWriter.finish();
            try{
                Runtime.getRuntime().exec("cmd /c start " + thisFileName);
            }catch(IOException e){}
        }
        return SUCCESS_TIP;
    }

    /**
     * 文件下载（失败了会返回一个有部分数据的Excel）
     * <p>1. 创建excel对应的实体对象
     * <p>2. 设置返回的 参数
     * <p>3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
     */
    @GetMapping("/download_checked")
    public void downloadChecked(HttpServletResponse response,
                         @RequestParam String selected,
                         @RequestParam(required = false) String excelType) throws IOException {
        logger.info("export_checked：", JSON.toJSONString(selected));
        JSONArray jsonArray = JSON.parseArray(selected);
        if(jsonArray.size() <= 0) {
            return;
        }
        List<String> list = jsonArray.toJavaList(String.class);
        List<Tjlb> tjlbs = tjlbService.selectTjlbListByItems(list);
        List<TjlbModel> tjlbModels = new ArrayList<>();
        for (Tjlb tjlb: tjlbs) {
            TjlbModel tjlbModel = new TjlbModel();
            ReflectUtil.copyValue(tjlb, tjlbModel);
            if(StringUtils.isNotBlank(tjlbModel.getFgcmc())) {
                tjlbModels.add(tjlbModel);
            }
        }
        if(StringUtils.isNotBlank(excelType) && Objects.equals(excelType, "03")) {
            String thisFileName = fileName + System.currentTimeMillis() + ExcelTypeEnum.XLS.getValue();
            // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode(thisFileName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            EasyExcel.write(response.getOutputStream(), TjlbModel.class).excelType(ExcelTypeEnum.XLS).sheet(sheetName).doWrite(tjlbModels);
        } else {
            String thisFileName = fileName + System.currentTimeMillis() + ExcelTypeEnum.XLSX.getValue();
            // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode(thisFileName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            EasyExcel.write(response.getOutputStream(), TjlbModel.class).sheet(sheetName).doWrite(tjlbModels);
        }
    }

    /**
     * 内部生产记录导出下载（失败了会返回一个有部分数据的Excel）
     * <p>1. 创建excel对应的实体对象
     * <p>2. 设置返回的 参数
     * <p>3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
     */
    @GetMapping("/download")
    public void download(HttpServletResponse response,
                         @RequestParam(required = false) String beginTime,
                         @RequestParam(required = false) String endTime,
                         @RequestParam(required = false) String outBeginTime,
                         @RequestParam(required = false) String outEndTime,
                         @RequestParam(required = false) String inBeginTime,
                         @RequestParam(required = false) String inEndTime,
                         @RequestParam(required = false) String gcmc,
                         @RequestParam(required = false) String excelType) throws IOException {
        List<Tjlb> tjlbs = tjlbService.selectTjlbList(beginTime, endTime, gcmc, outBeginTime, outEndTime, inBeginTime, inEndTime);
        List<Tjlb> tjlbs1 = tjlbService.selectTjlbCount(beginTime, endTime, gcmc, outBeginTime, outEndTime, inBeginTime, inEndTime);
        List<TjlbModel> tjlbModels = new ArrayList<>();
        for (Tjlb tjlb: tjlbs) {
            TjlbModel tjlbModel = new TjlbModel();
            ReflectUtil.copyValue(tjlb, tjlbModel);
            if(StringUtils.isNotBlank(tjlbModel.getFgcmc())) {
                tjlbModels.add(tjlbModel);
            }
        }

        List<TjlbCountModel> tjlbCountModels = new ArrayList<>();
        for (Tjlb tjlb: tjlbs1) {
            TjlbCountModel tjlbModel = new TjlbCountModel();
            ReflectUtil.copyValue(tjlb, tjlbModel);
            if(StringUtils.isNotBlank(tjlbModel.getFgcmc())) {
                tjlbCountModels.add(tjlbModel);
            }
        }
        String thisFileName;
        //ExcelWriterBuilder excelWriterBuilder;
        ExcelWriter excelWriter;
        if(StringUtils.isNotBlank(excelType) && Objects.equals(excelType, "03")) {
            thisFileName = fileName + System.currentTimeMillis() + ExcelTypeEnum.XLS.getValue();
            excelWriter = EasyExcel.write(response.getOutputStream()).excelType(ExcelTypeEnum.XLS).build();
            //excelWriterBuilder = EasyExcel.write(response.getOutputStream(), TjlbModel.class).excelType(ExcelTypeEnum.XLS);
        } else {
            thisFileName = fileName + System.currentTimeMillis() + ExcelTypeEnum.XLSX.getValue();
            excelWriter = EasyExcel.write(response.getOutputStream()).excelType(ExcelTypeEnum.XLSX).build();
            //excelWriterBuilder = EasyExcel.write(response.getOutputStream(), TjlbModel.class).excelType(ExcelTypeEnum.XLSX);
        }
        if(StringUtils.isNotBlank(thisFileName) && excelWriter != null &&
                (!tjlbModels.isEmpty() || !tjlbCountModels.isEmpty())) {
            try {
                // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
                response.setContentType("application/vnd.ms-excel");
                response.setCharacterEncoding("utf-8");
                // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
                String fileName = URLEncoder.encode(thisFileName, "UTF-8");
                response.setHeader("Content-disposition", "attachment;filename=" + fileName);
                if(!tjlbModels.isEmpty()) {
                    WriteSheet writeSheet1 = EasyExcel.writerSheet(0, "内部生产记录").head(TjlbModel.class).build();
                    excelWriter.write(tjlbModels, writeSheet1);
                }
                if(!tjlbCountModels.isEmpty()) {
                    WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "内部统计信息").head(TjlbCountModel.class).build();
                    excelWriter.write(tjlbCountModels, writeSheet2);
                }
                excelWriter.finish();
            } catch (Exception e) {
                e.printStackTrace();
                logger.error("导出内部生产记录出错", e);
            }
//            // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
//            response.setContentType("application/vnd.ms-excel");
//            response.setCharacterEncoding("utf-8");
//            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
//            String fileName = URLEncoder.encode(thisFileName, "UTF-8");
//            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
//            excelWriterBuilder.sheet(sheetName).doWrite(tjlbModels);
        }
    }

    /**
     * 对外生产记录导出文件下载（失败了会返回一个有部分数据的Excel）
     * <p>1. 创建excel对应的实体对象
     * <p>2. 设置返回的 参数
     * <p>3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
     */
    @GetMapping("/download_out")
    public void downloadOut(HttpServletResponse response,
                        @RequestParam(required = false) String beginTime,
                        @RequestParam(required = false) String endTime,
                        @RequestParam(required = false) String outBeginTime,
                        @RequestParam(required = false) String outEndTime,
                        @RequestParam(required = false) String inBeginTime,
                        @RequestParam(required = false) String inEndTime,
                        @RequestParam(required = false) String gcmc,
                        @RequestParam(required = false) String excelType) throws IOException {
        List<Tjlb> tjlbs = tjlbService.selectTjlbListOut(beginTime, endTime, gcmc, outBeginTime, outEndTime, inBeginTime, inEndTime);
        List<Tjlb> tjlbs1 = tjlbService.selectTjlbCountOut(beginTime, endTime, gcmc, outBeginTime, outEndTime, inBeginTime, inEndTime);
        List<TjlbModel> tjlbModels = new ArrayList<>();
        for (Tjlb tjlb: tjlbs) {
            TjlbModel tjlbModel = new TjlbModel();
            ReflectUtil.copyValue(tjlb, tjlbModel);
            if(StringUtils.isNotBlank(tjlbModel.getFgcmc())) {
                tjlbModels.add(tjlbModel);
            }
        }

        List<TjlbCountModel> tjlbCountModels = new ArrayList<>();
        logger.info(JSON.toJSONString(tjlbs1));
        for (Tjlb tjlb: tjlbs1) {
            TjlbCountModel tjlbModel = new TjlbCountModel();
            ReflectUtil.copyValue(tjlb, tjlbModel);
            if(StringUtils.isNotBlank(tjlbModel.getFgcmc())) {
                tjlbCountModels.add(tjlbModel);
            }
        }
        if(!tjlbModels.isEmpty() || !tjlbCountModels.isEmpty()) {
            StringBuilder condition = new StringBuilder();
            condition.append("select Frwdh,Fsybh,Ftbj,isnull(b.ypbh,'') as Fypbh from Mrwd a ");
            condition.append("left join Mphb b on substring(a.Fphbno, 4, len(a.Fphbno)-3) = b.pfdm ");
            condition.append("where Frwdh in(");
            for (TjlbModel model: tjlbModels) {
                String Frwdh = model.getFrwno();
                condition.append("'").append(Frwdh).append("',");
            }
            String conditionStr = condition.toString();
            for (TjlbCountModel model: tjlbCountModels) {
                String Frwdh = model.getFrwdh();
                if (!conditionStr.contains("'" + Frwdh + "'")) {
                    condition.append("'").append(Frwdh).append("',");
                }
            }
            condition = condition.deleteCharAt(condition.length()-1);
            condition.append(")");
            System.out.printf(condition.toString());
            try {
                List<MrwdModel> mrwdModels = sqlServerJdbcTemplate.query(condition.toString(), new Object[]{}, new BeanPropertyRowMapper<>(MrwdModel.class));
                if(!mrwdModels.isEmpty() && !tjlbModels.isEmpty()) {
                    for (TjlbModel model: tjlbModels) {
                        String Frwdh = model.getFrwno();
                        for(MrwdModel mrwdModel : mrwdModels) {
                            if(mrwdModel.getFrwdh().equals(Frwdh)) {
                                model.setFtbj(mrwdModel.getFtbj());
                                break;
                            }
                        }
                    }
                }

                if(!mrwdModels.isEmpty() && !tjlbCountModels.isEmpty()) {
                    for (TjlbCountModel model: tjlbCountModels) {
                        String Frwdh = model.getFrwdh();
                        for(MrwdModel mrwdModel : mrwdModels) {
                            if(mrwdModel.getFrwdh().equals(Frwdh)) {
                                model.setFtbj(mrwdModel.getFtbj());
                                break;
                            }
                        }
                    }
                }
            } catch (Exception e) {
                logger.error("查询zl数据库出错：{}", e.getMessage());
            }
        }

        String thisFileName;
        //ExcelWriterBuilder excelWriterBuilder;
        ExcelWriter excelWriter;
        if(StringUtils.isNotBlank(excelType) && "03".equals(excelType)) {
            thisFileName = fileName + System.currentTimeMillis() + ExcelTypeEnum.XLS.getValue();
            excelWriter = EasyExcel.write(response.getOutputStream()).excelType(ExcelTypeEnum.XLS).build();
            //excelWriterBuilder = EasyExcel.write(response.getOutputStream(), TjlbModel.class).excelType(ExcelTypeEnum.XLS);
        } else {
            thisFileName = fileName + System.currentTimeMillis() + ExcelTypeEnum.XLSX.getValue();
            excelWriter = EasyExcel.write(response.getOutputStream()).excelType(ExcelTypeEnum.XLSX).build();
            //excelWriterBuilder = EasyExcel.write(response.getOutputStream(), TjlbModel.class);
        }
        if(StringUtils.isNotBlank(thisFileName) && excelWriter != null &&
                (!tjlbModels.isEmpty() || !tjlbCountModels.isEmpty())) {
            try {
                // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
                response.setContentType("application/vnd.ms-excel");
                response.setCharacterEncoding("utf-8");
                // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
                String fileName = URLEncoder.encode(thisFileName, "UTF-8");
                response.setHeader("Content-disposition", "attachment;filename=" + fileName);
                if(!tjlbModels.isEmpty()) {
                    WriteSheet writeSheet1 = EasyExcel.writerSheet(0, "外部生产记录").head(TjlbModel.class).build();
                    excelWriter.write(tjlbModels, writeSheet1);
                }
                if(!tjlbCountModels.isEmpty()) {
                    WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "外部统计信息").head(TjlbCountModel.class).build();
                    excelWriter.write(tjlbCountModels, writeSheet2);
                }
                excelWriter.finish();
            } catch (Exception e) {
                e.printStackTrace();
                logger.error("导出外部生产记录出错", e);
            }
//            // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
//            response.setContentType("application/vnd.ms-excel");
//            response.setCharacterEncoding("utf-8");
//            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
//            String fileName = URLEncoder.encode(thisFileName, "UTF-8");
//            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
//            excelWriterBuilder.sheet(sheetName).doWrite(tjlbModels);
        }
    }

    /**
     * 文件上传
     * <p>1. 创建excel对应的实体对象
     * <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器
     * <p>3. 直接读即可
     */
    @PostMapping("upload")
    @ResponseBody
    public Object upload(MultipartFile file) throws IOException {
        //EasyExcel.read(file.getInputStream(), TjlbModel.class, new UploadDataListener(uploadDAO)).sheet().doRead();
        return SUCCESS_TIP;
    }

    /**
     * 砂浆文件下载（失败了会返回一个有部分数据的Excel）
     * <p>1. 创建excel对应的实体对象
     * <p>2. 设置返回的 参数
     * <p>3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
     */
    @GetMapping("/download_sj")
    public void downloadSj(HttpServletResponse response,
                         @RequestParam(required = false) String beginTime,
                         @RequestParam(required = false) String endTime,
                         @RequestParam(required = false) String gcmc,
                         @RequestParam(required = false) String excelType) throws IOException {
        List<Tjlb> tjlbs = tjlbService.selectTjlbSjList(beginTime, endTime, gcmc);
        List<TjlbSjModel> tjlbModels = new ArrayList<>();
        for (Tjlb tjlb: tjlbs) {
            TjlbSjModel tjlbSjModel = new TjlbSjModel();
            ReflectUtil.copyValue(tjlb, tjlbSjModel);
            if(StringUtils.isNotBlank(tjlbSjModel.getFgcmc())) {
                tjlbModels.add(tjlbSjModel);
            }
        }
        easyExcelService.download(response, tjlbModels, TjlbSjModel.class, "砂浆记录", excelType);
    }

    /**
     * 文件下载（失败了会返回一个有部分数据的Excel）
     * <p>1. 创建excel对应的实体对象
     * <p>2. 设置返回的 参数
     * <p>3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
     */
    @GetMapping("/download_checked_sj")
    public void downloadCheckedSj(HttpServletResponse response,
                                @RequestParam String selected,
                                @RequestParam(required = false) String excelType) throws IOException {
        logger.info("export_checked：", JSON.toJSONString(selected));
        JSONArray jsonArray = JSON.parseArray(selected);
        if(jsonArray.size() <= 0) {
            return;
        }
        List<String> list = jsonArray.toJavaList(String.class);
        List<Tjlb> tjlbs = tjlbService.selectTjlbListByItems(list);
        List<TjlbSjModel> tjlbModels = new ArrayList<>();
        for (Tjlb tjlb: tjlbs) {
            TjlbSjModel tjlbModel = new TjlbSjModel();
            ReflectUtil.copyValue(tjlb, tjlbModel);
            if(StringUtils.isNotBlank(tjlbModel.getFgcmc())) {
                tjlbModels.add(tjlbModel);
            }
        }
        easyExcelService.download(response, tjlbModels, TjlbSjModel.class, "砂浆记录", excelType);
    }


    /**
     * 日统计
     */
    @GetMapping(value = "/download_rtj")
    public void countByDay(@RequestParam String start, @RequestParam String end,
                           @RequestParam String excelType, HttpServletResponse response) throws IOException {
        List<Tjlb> tjlbs = tjlbService.countByDay(start, end);
        List<TjlbRtjModel> list = new ArrayList<>();
        for (Tjlb tjlb: tjlbs) {
            TjlbRtjModel tjlbRtjModel = new TjlbRtjModel();
            ReflectUtil.copyValue(tjlb, tjlbRtjModel);
            if(StringUtils.isNotBlank(tjlbRtjModel.getFgcmc())) {
                list.add(tjlbRtjModel);
            }
        }
        easyExcelService.download(response, list, TjlbRtjModel.class, "日统计", excelType);
    }

    /**
     * 送检专用
     * 查询任务单号、配合比编号、流水号、样品编号等
     */
    @GetMapping(value = "/download_sub")
    public void submission(@RequestParam String start, @RequestParam String end,
                           @RequestParam String excelType, HttpServletResponse response) throws IOException {
        // 统计数据
        List<TjlbSubModel> list = tjlbService.submission(start, end);

        if(list != null && !list.isEmpty()) {
            StringBuilder condition = new StringBuilder();
            condition.append("select Frwdh,Fsybh,Ftbj,isnull(b.ypbh,'') as Fypbh from Mrwd a ");
            condition.append("left join Mphb b on substring(a.Fphbno, 4, len(a.Fphbno)-3) = b.pfdm ");
            condition.append("and b.zt = '启用' ");
            condition.append("where Frwdh in(");
            for (TjlbSubModel model: list) {
                String Frwdh = model.getFrwdh();
                condition.append("'").append(Frwdh).append("',");
            }
            condition = condition.deleteCharAt(condition.length()-1);
            condition.append(")");
            System.out.printf(condition.toString());
            try {
                List<MrwdModel> mrwdModels = sqlServerJdbcTemplate.query(condition.toString(), new Object[]{}, new BeanPropertyRowMapper<>(MrwdModel.class));
                if(!mrwdModels.isEmpty()) {
                    for (TjlbSubModel model: list) {
                        String Frwdh = model.getFrwdh();
                        for(MrwdModel mrwdModel : mrwdModels) {
                            if(mrwdModel.getFrwdh().equals(Frwdh)) {
                                model.setSybh("P" + mrwdModel.getFsybh());
                                model.setLsh(mrwdModel.getFtbj());
                                model.setFypbh(mrwdModel.getFypbh());
                                break;
                            }
                        }
                    }
                }
            } catch (Exception e) {
                logger.error("查询zl数据库出错：{}", e.getMessage());
            }
        }
        easyExcelService.download(response, list, TjlbSubModel.class, "送检统计", excelType);
    }

    /**
     * 耗材统计
     */
    @GetMapping(value = "/download_cm")
    public void consumableMaterialCount(@RequestParam String start, @RequestParam String end,
                           @RequestParam String excelType, HttpServletResponse response) throws IOException {
        List<TjlbHctjModel> list = tjlbService.consumableMaterialCount(start, end);

        easyExcelService.download(response, list, TjlbHctjModel.class, "耗材统计", excelType);
    }

    /**
     * 对外耗材统计
     */
    @GetMapping(value = "/download_cm_out")
    public void consumableMaterialCountOut(@RequestParam String start, @RequestParam String end,
                                        @RequestParam String excelType, HttpServletResponse response) throws IOException {
        List<TjlbHctjModel> list = tjlbService.consumableMaterialCountOut(start, end);

        easyExcelService.download(response, list, TjlbHctjModel.class, "对外耗材统计", excelType);
    }
}
